package com.c5c7.dao;

import com.c5c7.bean.NoticeBean;
import com.c5c7.util.DBConnection;
import com.c5c7.util.Time;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class NoticeDao {
    DBConnection DBConn= new DBConnection();
    public List<NoticeBean> getList(int page){
        // todo 后期添加分页功能
        Connection conn = DBConn.getConn();
        String sql = "select * from notice limit "+(page-1)*10+",10";
        List<NoticeBean> list = new ArrayList<NoticeBean>();
        try {
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            while (rs.next()){
                NoticeBean NoticeBean = new NoticeBean();
                NoticeBean.setNoticeId(rs.getInt("NoticeId"));
                NoticeBean.setNoticeTitle(rs.getString("NoticeTitle"));
                NoticeBean.setNoticeContent(rs.getString("NoticeContent"));
                NoticeBean.setNoticeTime(rs.getString("NoticeTime"));
                NoticeBean.setAdminName(rs.getString("AdminName"));
                list.add(NoticeBean);
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        }
        return list;
    }

    /**
     * 添加新闻
     * @param
     * @return
     */
    public int add(NoticeBean notice){
        Connection conn = DBConn.getConn();
        String sql = "insert into notice value(null,?,?,?,?)";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, notice.getNoticeTitle());
            pmst.setString(2, notice.getNoticeContent());
            pmst.setString(3, Time.getDate());
            pmst.setString(4, notice.getAdminName());
            return pmst.executeUpdate(); // 执行添加 返回受影响的行数
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    /**
     * 更新功能
     * @param
     * @return
     */
    public int update(NoticeBean notice){
        Connection conn = DBConn.getConn();
        String sql = "update notice set NoticeTitle=?, NoticeContent=?, NoticeTime=?, AdminName=? where NoticeId=?";
        try {
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setString(1, notice.getNoticeTitle());
            pmst.setString(2, notice.getNoticeContent());
            pmst.setString(3, Time.getDate());
            pmst.setString(4, notice.getAdminName());
            pmst.setInt(5, notice.getNoticeId());
            System.out.println(pmst.toString());
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }

    public int delete(int NoticeId){
        Connection conn = DBConn.getConn();
        try {
            String sql = "delete from notice where NoticeId=?";
            PreparedStatement pmst = conn.prepareStatement(sql);
            pmst.setInt(1, NoticeId);
            return pmst.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return 0;
    }
    public int count(){
        Connection conn = DBConn.getConn();
        try {
            String sql = "select count(*) from notice";
            Statement statement = conn.createStatement();
            ResultSet rs = statement.executeQuery(sql);
            if(rs.next()){
                return rs.getInt(1);
            }
        }catch (SQLException e){
            e.printStackTrace();
        }
        return 0;
    }
}
